﻿using System;
using System.Data.SqlClient;


namespace VisualSearch.NorthwindSearch
{
    public class EmployeeSearch : VisualSearch.Core.Provider
    {
        public override VisualSearch.Core.SearchSuggestion Search(string keyword)
        {
            VisualSearch.Core.SearchSuggestion searchSuggestion = new VisualSearch.Core.SearchSuggestion { Query = keyword };
            var connectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["Northwind"];
            using (var sqlConnection = new SqlConnection(connectionString.ConnectionString))
            {
                sqlConnection.Open();
                using (SqlCommand command = new SqlCommand("SELECT EmployeeId, FirstName + ' ' + LastName AS FullName, Title, HomePhone FROM Employees WHERE FirstName LIKE @keyword OR LastName LIKE @keyword", sqlConnection))
                {
                    command.Parameters.Add(new SqlParameter("@keyword", String.Format("%{0}%", keyword)));
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            searchSuggestion.Section.Add(
                                new VisualSearch.Core.Item
                                {
                                    Id = reader.GetInt32(0).ToString(),
                                    Text = reader.GetString(1),
                                    Description = reader.GetString(2),
                                    Url = "http://localhost#",
                                    Image = new VisualSearch.Core.Image
                                    {
                                        Alt = reader.GetString(1),
                                        Source = String.Format("{0}?id={1}", base.PhotoUrl, reader.GetInt32(0)),
                                        Width = 16,
                                        Height = 18
                                    }
                                }
                            );
                        }
                    }
                }
                sqlConnection.Close();
                return searchSuggestion;
            }
        }
        public override byte[] GetPhotoStream(string Id)
        {
            var connectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["Northwind"];
            using (var sqlConnection = new SqlConnection(connectionString.ConnectionString))
            {
                sqlConnection.Open();
                using (SqlCommand command = new SqlCommand("SELECT Photo FROM Employees WHERE EmployeeId = @EmployeeId", sqlConnection))
                {
                    command.Parameters.Add(new SqlParameter("@EmployeeId", Id));
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if(reader.Read())
                        {
                            var size = reader.GetBytes(0,0,null,0,0)-78;
                            byte[] buffer = new byte[size];
                            reader.GetBytes(0, 78, buffer, 0, (int)size);
                            return buffer;
                        }
                        return null;
                    }
                }
                sqlConnection.Close();
            }
        }
    }
}
